from fastapi import APIRouter, HTTPException
from pydantic import BaseModel

from utils.sql import MySQL

db = MySQL(host='localhost', port=3306, user='root', password='121300', database='fastapi')

app01 = APIRouter()


def create_table(table_name, create_sql):
    """
    通用的创建表函数
    :param table_name: 表名
    :param create_sql: 创建表的 SQL 语句
    :return: 包含表名和创建结果的字典
    """
    rows_affected = db.execute_update(create_sql)
    if rows_affected >= 0:
        result = f"{table_name} 表创建成功"
    else:
        result = f"{table_name} 表创建失败"
    return {table_name: result}


@app01.get("/create/")
def create_all_tables():
    # 班级表
    class_table_sql = """
    CREATE TABLE IF NOT EXISTS classes (
        class_id INT AUTO_INCREMENT PRIMARY KEY,
        class_name VARCHAR(255) NOT NULL,
        description TEXT
    )
    """
    class_result = create_table("classes", class_table_sql)

    # 学生表
    student_table_sql = """
    CREATE TABLE IF NOT EXISTS students (
        student_id INT AUTO_INCREMENT PRIMARY KEY,
        student_name VARCHAR(255) NOT NULL,
        age INT,
        gender VARCHAR(10),
        class_id INT,
        FOREIGN KEY (class_id) REFERENCES classes(class_id),
        course_id INT,
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
    )
    """
    student_result = create_table("students", student_table_sql)

    # 课程表
    course_table_sql = """
    CREATE TABLE IF NOT EXISTS courses (
        course_id INT AUTO_INCREMENT PRIMARY KEY,
        course_name VARCHAR(255) NOT NULL,
        credit INT
    )
    """
    course_result = create_table("courses", course_table_sql)

    # 选课表
    course_selection_table_sql = """
    CREATE TABLE IF NOT EXISTS course_selection (
        selection_id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT,
        course_id INT,
        FOREIGN KEY (student_id) REFERENCES students(student_id),
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
    )
    """
    course_selection_result = create_table("course_selection", course_selection_table_sql)

    # 分数表
    score_table_sql = """
        CREATE TABLE IF NOT EXISTS scores (
            score_id INT AUTO_INCREMENT PRIMARY KEY,
            selection_id INT,
            midterm_score FLOAT,
            final_score FLOAT,
            FOREIGN KEY (selection_id) REFERENCES course_selection(selection_id)
        )
        """
    score_result = create_table("scores", score_table_sql)

    all_results = {
        **class_result,
        **student_result,
        **course_result,
        **course_selection_result,
        **score_result
    }
    return all_results


# 定义 Pydantic 模型，用于接收客户端发送的班级信息
class ClassCreate(BaseModel):
    class_name: str
    description: str


# 定义插入班级数据的路由
@app01.post("/classes/")
def add_class(class_item: ClassCreate):
    try:
        # 插入数据的 SQL 语句
        insert_sql = "INSERT INTO classes (class_name, description) VALUES (%s, %s)"
        # 执行 SQL 语句，插入数据
        rows_affected = db.execute_update(insert_sql, (class_item.class_name, class_item.description))
        if rows_affected > 0:
            return {"message": "数据插入成功", "data": class_item.dict()}
        else:
            return {"message": "数据插入失败"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"数据插入失败: {str(e)}")


class Courses(BaseModel):
    class_name: str
    description: str


@app01.post("/courses/")
def add_class(class_item: Courses):
    try:
        # 插入数据的 SQL 语句
        insert_sql = "INSERT INTO courses (course_name, credit) VALUES (%s, %s)"
        # 执行 SQL 语句，插入数据
        rows_affected = db.execute_update(insert_sql, (class_item.course_name, class_item.credit))
        if rows_affected > 0:
            return {"message": "数据插入成功", "data": class_item.dict()}
        else:
            return {"message": "数据插入失败"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"数据插入失败: {str(e)}")


class Students(BaseModel):
    student_name: str
    age: int
    gender: str
    class_id: int
    course_id: int


# 定义插入班级数据的路由
@app01.post("/students/")
def add_class(class_item: Students):
    try:
        # 插入数据的 SQL 语句
        insert_sql = "INSERT INTO students (student_name, age, gender, class_id, course_id) VALUES (%s, %s, %s, %s, %s)"
        # 执行 SQL 语句，插入数据
        rows_affected = db.execute_update(insert_sql, (class_item.student_name, class_item.age,
                                                       class_item.gender, class_item.class_id,
                                                       class_item.course_id))
        if rows_affected > 0:
            return {"message": "数据插入成功", "data": class_item.dict()}
        else:
            return {"message": "数据插入失败"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"数据插入失败: {str(e)}")


class SelectCourse(BaseModel):
    student_id: int
    course_id: int


# 定义插入班级数据的路由
@app01.post("/select/")
def add_class(class_item: SelectCourse):
    try:
        # 插入数据的 SQL 语句
        insert_sql = "INSERT INTO course_selection (student_id, course_id) VALUES (%s, %s)"
        # 执行 SQL 语句，插入数据
        rows_affected = db.execute_update(insert_sql, (class_item.student_id, class_item.course_id))
        if rows_affected > 0:
            return {"message": "数据插入成功", "data": class_item.dict()}
        else:
            return {"message": "数据插入失败"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"数据插入失败: {str(e)}")


class Score(BaseModel):
    selection_id: int
    midterm_score: float
    final_score: float


# 定义插入分数数据的路由
@app01.post("/score/")
def add_score(score_item: Score):
    try:
        # 插入数据的 SQL 语句，将表名修改为 scores
        insert_sql = "INSERT INTO scores (selection_id, midterm_score, final_score) VALUES (%s, %s, %s)"
        # 执行 SQL 语句，插入数据
        rows_affected = db.execute_update(insert_sql, (score_item.selection_id, score_item.midterm_score,
                                                       score_item.final_score))
        if rows_affected > 0:
            return {"message": "数据插入成功", "data": score_item.dict()}
        else:
            return {"message": "数据插入失败"}
    except Exception as e:
        # 打印详细的错误信息
        print(f"详细错误信息: {str(e)}")
        raise HTTPException(status_code=500, detail=f"数据插入失败: {str(e)}")
